﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using Table_Dal;
namespace DAL
{
    public class ThiSinhDAL:DataAccessLayer
    {
        DataDataContext dt;
        public ThiSinhTbl ThongTinThiSinh(string p1)
        {
            dt = GetKetnoi();
            IEnumerable<ThiSinh> ts = dt.ThiSinhs.Where(h => h.MSSV == p1);
            ThiSinhTbl thisinh = new ThiSinhTbl();
            foreach (ThiSinh item in ts)
            {
                thisinh.MSSV = item.MSSV;
                thisinh.MatKhau = item.MATKHAU;
                thisinh.TenSV = item.TENSV;
                

            }
            return thisinh;
        }

        public List<ThiSinhTbl> LayTatCaThiSinh_Diem(string p)
        {
            dt = GetKetnoi();
            var ds = from i in dt.ThiSinhs
                     join j in dt.DIEMs
                     on i.MSSV equals j.MSSV
                     where j.MADETHI == p
                     select new { i.MSSV,i.TENSV,j.DIEM1};
            List<ThiSinhTbl> dsts = new List<ThiSinhTbl>();
            foreach (var item in ds)
            {
                ThiSinhTbl ts = new ThiSinhTbl();
                ts.MSSV = item.MSSV;
                ts.TenSV = item.TENSV;
                if (item.DIEM1 == -1||item.DIEM1 ==null)
                    ts.MatKhau = "Chưa thi";
                else ts.MatKhau = item.DIEM1.ToString();
                dsts.Add(ts);
            }
            return dsts;
        }

        public List<ThiSinhTbl> LayTatCaThiSinh()
        {
            dt = GetKetnoi();
            IEnumerable<ThiSinh> q = dt.ThiSinhs;
            List<ThiSinhTbl> ds = new List<ThiSinhTbl>();
            foreach (ThiSinh item in q)
            {
                ThiSinhTbl ts = new ThiSinhTbl();
                ts.MSSV = item.MSSV;
                ts.TenSV = item.TENSV;
                ts.MatKhau = item.MATKHAU;
                ds.Add(ts);
            }
            return ds;
        }

        public void Sua(ThiSinhTbl tsinh)
        {
            dt = GetKetnoi();
            ThiSinh ts = dt.ThiSinhs.Where(i => i.MSSV == tsinh.MSSV).Single();
            ts.MATKHAU = tsinh.MatKhau;
            ts.TENSV = tsinh.TenSV;
            dt.SubmitChanges();
        }

        public void Them(ThiSinhTbl tsinh)
        {
            dt = GetKetnoi();
            ThiSinh ts = new ThiSinh();
            ts.MATKHAU = tsinh.MatKhau;
            ts.TENSV = tsinh.TenSV;
            ts.MSSV = tsinh.MSSV;
            dt.ThiSinhs.InsertOnSubmit(ts);
            dt.SubmitChanges();
        }
        public bool KiemtraKey(string Ma)
        {
            dt = GetKetnoi();
            IEnumerable<ThiSinh> q = dt.ThiSinhs.Where(i => i.MSSV == Ma);
            foreach (var item in q)
            {
                if (item.MSSV == Ma)
                    return true;
            }
            return false;
        }

        public void Xoa(string item)
        {
            try
            {
                dt = GetKetnoi();
                ThiSinh ts = dt.ThiSinhs.SingleOrDefault(j => j.MSSV == item);
                dt.ThiSinhs.DeleteOnSubmit(ts);
                dt.SubmitChanges();
            }
            catch
            {

            }
        }
    }
}
